SQL DECODE |
您所在的位置：网站首页 › sql url_decode › SQL DECODE |
SQL DECODE
|
Summary: in this tutorial, you will learn how to use the SQL DECODE() function to add if-then-else logic to queries. Introduction to SQL DECODE() functionThe SQL DECODE() function allows you to add procedure if-then-else logic to queries. Let’s see the following example: SELECT DECODE(1,1,'Equal'); Code language: SQL (Structured Query Language) (sql)In this example, the DECODE() function compares the first argument (one) with the second argument (also one). Because the first argument equals the second one, the function returns the third argument which is the string Equal. The DECODE() function above works the same as the following IF statement: IF 1 = 1 THEN RETURN 'Equal'; END IF; Code language: SQL (Structured Query Language) (sql)The following example returns NULL because the first argument is not equal to the second one. SELECT DECODE(1,2, 'Equal'); Code language: SQL (Structured Query Language) (sql)It works like the following statement: IF 1 = 2 THEN RETURN 'Equal'; END IF; Code language: SQL (Structured Query Language) (sql)If you want to specify the value when the first argument is not equal to the second one, you use the following form of the DECODE() function: SELECT DECODE(1,2, 'Equal', 'Not Equal'); Code language: SQL (Structured Query Language) (sql)It works like the following IF-THEN-ELSE statement: IF 1 = 2 THEN RETURN 'Equal'; ELSE RETURN 'Not Equal'; END IF; Code language: SQL (Structured Query Language) (sql)In general, you can compare the first argument of the DECODE() function with a list of arguments as shown in the following example: SELECT DECODE (2, 1, 'Equal 1', 2, 'Equal 2'); Code language: SQL (Structured Query Language) (sql)This example works as the following IF-THEN-ELSE IF statement: IF 2 = 1 THEN RETURN 'Equal 1'; ELSE IF 2 = 2 RETURN 'Equal 2'; END IF; Code language: SQL (Structured Query Language) (sql)See the following example: SELECT DECODE(3,1, 'Equal 1,', 2, 'Equal 2', 'Not Equal 1 or 2'); Code language: SQL (Structured Query Language) (sql)This example works like the following IF-THEN-ELSEIF-ELSE statement: IF 3 = 1 THEN RETURN 'Equal 1'; ELSE IF 3 =2 THEN RETURN 'Equal 2'; ELSE RETURN 'Not Equal 1 or 2'; END IF; Code language: SQL (Structured Query Language) (sql)SQL DECODE() function syntaxThe following illustrates the syntax of the SQL DECODE() function: DECODE (e , s1, r1[, s2, r2], ...,[,sn,rn] [, d]); Code language: SQL (Structured Query Language) (sql)In this syntax: e is the argument that to be searched for or compared with other argument s1, s2, … sn.s1, s2, …, or sn is the expression to search for. The function compares the first argument with s1, s2, …, sn sequentially. If any comparison e = s1, e = s2, …, e = sn return true, the DECODE() function terminates the evaluation and returns the result.r1, r2, …, or rn is the returned result when ei = si.d is an expression to return when e does not equal to any argument s1, s,2, … or sn.SQL DECODE() exampleWe will use the employees table in the sample database for demonstration.  See the following query: SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY DECODE('S', 'F', first_name, 'L', last_name, 'S', salary); Code language: SQL (Structured Query Language) (sql)In this example, we used the DECODE() function in the ORDER BY clause. The DECODE() function returned the column to sort based on the input argument. In this case, the DECODE() function returned the salary column, therefore, the result set was sorted by salary column. In this tutorial, you have learned how to use the SQL DECODE() function to add the if-then-else logic to a query. Was this tutorial helpful ? |
【本文地址】